/*
 * DataBases 445 Library DBMS 
 */

package gui;

import java.awt.Dimension;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.BoxLayout;
import javax.swing.JButton;
import javax.swing.JPanel;
import javax.swing.JTextArea;

import dbConnection.Database;


/**
 * Class to create the gui for the User Search Page for the
 * User view. This includes the necessary front end, 
 * and back end code.
 * 
 * @author Prentice, Arslan, Herndon
 *
 */
@SuppressWarnings("serial")
public class UserSearchResult extends JPanel {

	/** The itemID of the item. */ 
	public int itemID;

	/** The ID of the currently logged in user */
	public int userID;

	/** The Database object for the class. */
	public Database DB;

	final private JButton checkin, checkout, hold;

	/**
	 * Initializes the Gui + Backend code for the ItemSearchResult 
	 * panel object.
	 * 
	 * @param userID
	 * @param inventoryID
	 * @param DB
	 */
	public UserSearchResult(int userID, int itemID, Database DB) {
		this.DB = DB;
		this.itemID = itemID;
		this.userID = userID;

		setLayout(new BoxLayout(this,BoxLayout.X_AXIS));
		setMaximumSize(new Dimension(600,120));

		final JTextArea summary = new JTextArea();
		summary.setText(getSummary());

		checkin = new JButton("Check In");
		checkout = new JButton("Check Out");
		hold = new JButton("Place Hold");
		updateButtons();

		checkin.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				//update the book's status in the db to be checked in
				if(UserSearchResult.this.DB.con != null) {
					try {
						Statement statement = UserSearchResult.this.DB.con.createStatement();
						statement.executeUpdate("UPDATE inventory SET UserId=0,CheckoutStatus=\"Checked In\"," +
								"DueDate=null " +
								"WHERE ItemId=" + UserSearchResult.this.itemID + ";");
					} catch (SQLException eee) {
						// do nothing
					}
				}
				updateButtons();
			}
		});

		checkout.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				//check out the book (pick one book with this itemID, set its status to the id of the current user
				if(UserSearchResult.this.DB.con != null) {
					try {
						java.util.Date today = new java.util.Date();
						int TwoWeeksInMilliseconds = 1209600000;
						Date sqlToday = new java.sql.Date(today.getTime() + TwoWeeksInMilliseconds);
						Statement statement = UserSearchResult.this.DB.con.createStatement();
						statement.executeUpdate("UPDATE inventory SET UserId=" + UserSearchResult.this.userID +
								",CheckoutStatus=\"Checked Out\",DueDate=\"" + sqlToday.toString() +
								"\" WHERE ItemId=" + UserSearchResult.this.itemID + ";");

					} catch (SQLException eee) {
						// do nothing
					}
				}
				updateButtons();
			}
		});

		hold.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e) {
				//put the book on hold (add a new row to the holds table)
				if(UserSearchResult.this.DB.con != null) {
					try {
						int inventoryId;
						Statement statement = UserSearchResult.this.DB.con.createStatement();
						ResultSet rs = statement.executeQuery("SELECT InventoryId FROM inventory WHERE " +
								"UserId=" + UserSearchResult.this.itemID + ";");
						if (!rs.equals(null) && rs.next()) {
							inventoryId = rs.getInt(1);
							statement = UserSearchResult.this.DB.con.createStatement();
							statement.executeUpdate("INSERT INTO hold VALUES (" + UserSearchResult.this.itemID +
									"," + inventoryId + "," + inventoryId/2 + ");");
						}
					} catch (SQLException eee) {
						// do nothing
					}
				}
			}
		});
		add(summary);
		add(checkin);
		add(checkout);
		add(hold);
	}

	public String getSummary() {
		//Get query from database, similar to admin search result, perhaps leave out inventory ID
		//if connection is successfully established, create statement
		if(this.DB.con == null) return "Database is currently inaccesible";
		Statement statement = null;	  
		try {
			statement = this.DB.con.createStatement();
		} catch (SQLException e) {
			return "Unable to create statement";
		}

		ResultSet resultSet = null;
		if(statement != null) {
			try {
				resultSet = statement.executeQuery("SELECT item.ItemId,item.Title,item.Author,inventory.Cond,inventory.ConditionLastChecked," +
						"item.Type,inventory.CheckoutStatus" +
						" FROM item NATURAL JOIN inventory WHERE " + 
						"inventory.ItemId=" + this.itemID + ";");
			} catch (SQLException e) {
				return "Unable to create statement";
			}
		}
		StringBuilder bd = new StringBuilder();
		//if resultset is received and is not empty,
		// iterate over resultset to get values
		try {
			if(resultSet != null && resultSet.next()) {
				bd.append("ItemId: " + resultSet.getString(1) + "\n");
				bd.append("Title: " + resultSet.getString(2) + "\n");
				bd.append("Author: " + resultSet.getString(3) + "\n");
				bd.append("Condition: " + resultSet.getString(4) + "\n");
				bd.append("Condition Last Checked: " + resultSet.getString(5) + "\n");
				bd.append("Genre: " + resultSet.getString(6) + "\n");
				bd.append("Status: " + resultSet.getString(7) + "\n");
			}
		} catch (SQLException e) {
			System.out.println("Unable to iterate over resultset");
		}

		return bd.toString(); 
	}

	/**
	 * Enables or disables buttons to reflect if user can checkout, hold, or check in items
	 */
	public void updateButtons() {
		boolean isOnHold = false;	    
		if(this.DB.con != null) {
			try {
				//Query to see if the user has this item on hold
				Statement statement = this.DB.con.createStatement();
				ResultSet resultSet = statement.executeQuery("SELECT hold.UserId " +
						"FROM inventory NATURAL JOIN hold " +
						"WHERE inventory.ItemId=" + this.itemID + " AND " +
						"inventory.UserId=" + this.userID + ";");

				//if resultset is received and is not empty,
				// iterate over resultset to get values
				if (!resultSet.equals(null) && resultSet.next()) {	
					isOnHold = true;
				}
			} catch (SQLException eee) {
				// 
			}
		}
		hold.setEnabled(!isOnHold);

		//Query to see if the user has this item checked out
		boolean isCheckedOut = false;    
		if(this.DB.con != null) {
			try {
				Statement statement = this.DB.con.createStatement();
				ResultSet resultSet = statement.executeQuery("SELECT inventory.CheckOutStatus " +
						"FROM inventory NATURAL JOIN user " +
						"WHERE inventory.ItemId=" + this.itemID + 
						" AND user.UserId=" + this.userID + ";");

				if (resultSet.next() && resultSet.getString(1).equals("Checked Out")) {	
					isCheckedOut = true;
				}

			} catch (SQLException eee) {
				// 
			}
		} 
		//Query to see if user can check out items (no fines/overdue items)
		boolean canCheckOut = true;
		if(this.DB.con != null) {
			java.util.Date today = new java.util.Date();
			Date sqlToday = new java.sql.Date(today.getTime());
			try {
				Statement statement = this.DB.con.createStatement();
				ResultSet resultSet = statement.executeQuery("SELECT user.Fines, " +
						"inventory.DueDate" +
						"FROM inventory NATURAL JOIN user " +
						"WHERE user.UserId=" + this.userID + " AND " +
						"inventory.CheckoutStatus=\"Checked Out\";");

				if (resultSet.next()) {	
					if (resultSet.getDouble(1) > 0 || (resultSet.getDate(2) != null &&
							resultSet.getDate(2).before(sqlToday))) {
						canCheckOut = false;
					}
				}
			} catch (SQLException eee) {
				// 
			}
		} 

		checkout.setEnabled(!isCheckedOut && canCheckOut);
		checkin.setEnabled(isCheckedOut);
	}
}
